package com.tuanzi.loan.business.repository.miniApps.articleLibrary.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanMiniAppsArticle.LOAN_MINI_APPS_ARTICLE;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanMiniAppsRelation.LOAN_MINI_APPS_RELATION;


import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;


import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.jooq.Condition;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.SelectQuery;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;


import com.google.common.collect.Lists;
import com.tuanzi.loan.business.entity.miniApps.articleLibrary.MiniAppsArticle;
import com.tuanzi.loan.business.entity.miniApps.articleLibrary.MiniAppsArticleRelations;
import com.tuanzi.loan.business.entity.miniApps.articleLibrary.MiniAppsRelation;
import com.tuanzi.loan.business.repository.miniApps.articleLibrary.HealthHeadlineRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.miniApps.articleLibrary.HealthHeadlineRequest;
import com.tuanzi.loan.vo.response.miniApps.articleLibrary.HealthHeadlineResponse;
import com.tuanzi.server.loan_base.constant.miniapps.article.ArticleConstant;
import com.tuanzi.server.loan_base.util.DateUtils;

@Repository
public class HealthHeadlineRepositoryImpl extends JOOQBaseRepositoryPlus implements HealthHeadlineRepository{
	
	private static List<MiniAppsRelation> tabList = null;
	private static List<MiniAppsRelation> miniAppsList = null;
	private static List<MiniAppsArticle> sourceList = null;
	private static List<MiniAppsRelation> advertiseList = null;
	private static List<MiniAppsArticle> articleList = null;
	private static Map<String ,MiniAppsRelation> tabMap = null;
	private static Map<String ,MiniAppsRelation> miniAppsMap = null;
	private static Map<String ,MiniAppsRelation> advertiseMap = null;

	@Override
	protected TableImpl getTable() {
		return LOAN_MINI_APPS_ARTICLE;
	}
	
	public void cleanCache() {
		tabList = null;
		miniAppsList = null;
		advertiseList = null;
		sourceList = null;
		articleList = null;
		tabMap = null;
		miniAppsMap = null;
		advertiseMap = null;
	}
	
	public List<MiniAppsRelation> getAdvertiseList() {
		loadRelationType();
		return advertiseList;
	}
	
	public List<MiniAppsArticle> getSourceList() {
		loadArticleSource();
		return sourceList;
	}
	
	public List<MiniAppsRelation> getTabList() {
		loadRelationType();
		return tabList;
	}
	
	public List<MiniAppsRelation> getMiniAppsList() {
		loadRelationType();
		return miniAppsList;
	}
	
	public Map<String ,MiniAppsRelation> getAdvertiseMap() {
		loadRelationType();
		return advertiseMap;
	}
	
	public Map<String ,MiniAppsRelation> getTabMap() {
		loadRelationType();
		return tabMap;
	}
	
	public Map<String ,MiniAppsRelation> getMiniAppsMap() {
		loadRelationType();
		return miniAppsMap;
	}
	
	/**
	 * 获得推荐的文章列表
	 * @return
	 */
	public List<MiniAppsArticle> getRecommendArticleList() {
		if(articleList == null) {
			List<Object> paramList = Lists.newArrayList();
			StringBuffer sbf = new StringBuffer();
			sbf.append(" SELECT a.ID AS article_id ,a.TITLE AS title ");
			sbf.append(" FROM loan.loan_mini_apps_article a ,loan.loan_mini_apps_article_relations b ,loan.loan_mini_apps_relation c ");
			sbf.append(" WHERE a.ID = b.ARTICLE_ID AND b.RELATION_ID = c.ID AND c.TYPE = 'SORT' AND b.STATUS = 1 ");
			
			articleList = dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsArticle>() {
				@Override
				public MiniAppsArticle map(Record record) {
					MiniAppsArticle relation = new MiniAppsArticle();
					relation.setId(record.get("article_id", Long.class));
					relation.setTitle(record.get("title", String.class));
					return relation;
				}
			});
		}
		return articleList;
	}
	
	private void loadArticleSource() {
		if(sourceList == null) {
			sourceList = new ArrayList<MiniAppsArticle>();
			Condition condition = DSL.trueCondition();
			DateUtils.formatDateToYyyyMMdd(new DateTime().plusDays(-7).toDate());
			condition = condition.and(LOAN_MINI_APPS_ARTICLE.CREATE_TIME.ge(new Timestamp(new DateTime().plusDays(-7).toDate().getTime())));
			SelectQuery sql = dslContext.select(LOAN_MINI_APPS_ARTICLE.SOURCE_ID, LOAN_MINI_APPS_ARTICLE.SOURCE_NAME)
					.from(LOAN_MINI_APPS_ARTICLE).where(condition).groupBy(LOAN_MINI_APPS_ARTICLE.SOURCE_ID).getQuery();
			sql.fetch(record -> {
				MiniAppsArticle source = new MiniAppsArticle();
				source.setSourceId(record.get(0,String.class));
				source.setSourceName(record.get(1,String.class));
				sourceList.add(source);
				return source;
			});
		}
	}
	
	private void loadRelationType() {
		if(tabList == null || miniAppsList == null || advertiseList == null) {
			SelectQuery sql = dslContext.select(LOAN_MINI_APPS_RELATION.ID, LOAN_MINI_APPS_RELATION.RELATION_CODE
					, LOAN_MINI_APPS_RELATION.RELATION_NAME, LOAN_MINI_APPS_RELATION.TYPE)
					.from(LOAN_MINI_APPS_RELATION).getQuery();
			
			tabList = new ArrayList<MiniAppsRelation>();
			miniAppsList = new ArrayList<MiniAppsRelation>();
			advertiseList = new ArrayList<MiniAppsRelation>();
			tabMap = new HashMap<String, MiniAppsRelation>();
			miniAppsMap = new HashMap<String, MiniAppsRelation>();
			advertiseMap = new HashMap<String, MiniAppsRelation>();
			sql.fetch(record -> {
				MiniAppsRelation relation = new MiniAppsRelation();
				relation.setId(record.get(0,Long.class));
				relation.setRelationCode(record.get(1,String.class));
				relation.setRelationName(record.get(2,String.class));
				String type = record.get(3,String.class);
				relation.setType(type);
				
				if(ArticleConstant.ARTICLE_RELATION_TYPE_TAB.equals(type)) {
					tabList.add(relation);
					tabMap.put(relation.getRelationCode(), relation);
				}
				if(ArticleConstant.ARTICLE_RELATION_TYPE_MINI_APPS.equals(type)) {
					miniAppsList.add(relation);
					miniAppsMap.put(relation.getRelationCode(), relation);
				}
				if(ArticleConstant.ARTICLE_RELATION_TYPE_ADVERTISE.equals(type)) {
					advertiseList.add(relation);
					advertiseMap.put(relation.getRelationCode(), relation);
				}
				return relation;
			});
		}
	}
	
	public Page<HealthHeadlineResponse> getListPage(HealthHeadlineRequest searchRequest, Pageable pageable) {
		List<Object> paramList = Lists.newArrayList();
		StringBuilder  sql = new StringBuilder("SELECT a.ID as id ,a.SOURCE_ID as source_id ,a.SOURCE_NAME as source_name ,a.TITLE as title ,a.RECOMMEND_MAIN_IMAGE as recommend_main_image , ");
		sql.append(" a.ONLINE_STATUS as online_status ,t.STATUS as recommend_status ,a.CREATE_TIME as create_time ,a.ONLINE_TIME as online_time ");
		sql.append(" FROM loan.loan_mini_apps_article a ");
		sql.append(" LEFT JOIN "); 
		sql.append(" (SELECT ar.ARTICLE_ID as ARTICLE_ID , ar.STATUS AS status, ar.ONLINE_TIME AS recommend_time FROM loan.loan_mini_apps_article_relations ar , loan_mini_apps_relation rr ");
		sql.append(" WHERE rr.TYPE = 'SORT' AND ar.RELATION_ID = rr.id  ");
		sql.append(" ) t on a.id = t.ARTICLE_ID where 1 = 1 ");
		
		if(Objects.nonNull(searchRequest.getStartCreateDate())) { // 入库时间
			sql.append(" AND a.CREATE_TIME >= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getStartCreateDate()));
		}
		if(Objects.nonNull(searchRequest.getEndCreateDate())) {
			sql.append(" AND a.CREATE_TIME <= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndCreateDate()));
		}
		if(Objects.nonNull(searchRequest.getStartRecommendDate())) { // 推荐时间
			sql.append(" AND t.recommend_time >= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getStartRecommendDate()));
		}
		if(Objects.nonNull(searchRequest.getEndRecommendDate())) { 
			sql.append(" AND t.recommend_time <= ? ");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndRecommendDate()));
		}
		if (Objects.nonNull(searchRequest.getId()) && searchRequest.getId() != 0) {
			sql.append(" AND a.id = ? ");
			paramList.add(searchRequest.getId());
		}
		if (StringUtils.isNotEmpty(searchRequest.getTitle())) { // 文章标题
			sql.append(" AND a.title like ? ");
			paramList.add("%"+searchRequest.getTitle()+"%");
		}
		if(Objects.nonNull(searchRequest.getOnlineStatus())) { // 上线状态
			sql.append(" AND a.ONLINE_STATUS = ? ");
			paramList.add(searchRequest.getOnlineStatus());
		}
		if(Objects.nonNull(searchRequest.getRecommendStatus())) { // 推荐状态
			sql.append(" AND t.STATUS = ? ");
			paramList.add(searchRequest.getRecommendStatus());
		}
		if (!(Objects.isNull(searchRequest.getSourceIdList()) || searchRequest.getSourceIdList().isEmpty())) { // 文章来源
			sql.append(" AND a.SOURCE_ID in ("+listToSqlPart(searchRequest.getSourceIdList())+") ");
		}
		if (!(Objects.isNull(searchRequest.getTabCodeList()) || searchRequest.getTabCodeList().isEmpty())) { // 标签
			sql.append(" AND EXISTS (SELECT 1 FROM loan.loan_mini_apps_article_relations ar, loan.loan_mini_apps_relation r ");
			sql.append(" WHERE ar.RELATION_ID = r.id AND r.TYPE = 'TAB' AND r.RELATION_CODE in ("+listToSqlPart(searchRequest.getTabCodeList())+") AND a.ID = ar.ARTICLE_ID) ");
		}
		if(!(Objects.isNull(searchRequest.getMiniAppsCodeList()) || searchRequest.getMiniAppsCodeList().isEmpty())) { // 小程序
			sql.append(" AND EXISTS (SELECT 1 FROM loan.loan_mini_apps_article_relations ar, loan.loan_mini_apps_relation r "); 
			sql.append(" WHERE ar.RELATION_ID = r.id AND r.TYPE = 'MINI_APPS' AND r.RELATION_CODE in ("+listToSqlPart(searchRequest.getMiniAppsCodeList())+") AND a.ID = ar.ARTICLE_ID) ");
		}
		
		sql.append(" ORDER BY a.create_time DESC");
		
		Long count = dslContext.fetchOne("SELECT COUNT(*) FROM (" + sql + ") AS i", paramList.toArray()).into(Long.class);
		
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		return new PageImpl<HealthHeadlineResponse>(dslContext.fetch(sql + " LIMIT ?,? ", paramList.toArray()).map(new RecordMapper<Record, HealthHeadlineResponse>() {
			@Override
			public HealthHeadlineResponse map(Record record) {
				HealthHeadlineResponse response = new HealthHeadlineResponse();
				Long articleId = record.get("id", Long.class);
				response.setId(articleId);
				response.setSourceId(record.get("source_id", String.class));
				response.setSourceName(record.get("source_name", String.class));
				response.setTitle(record.get("title",String.class));
				response.setRecommendMainImage(record.get("recommend_main_image",String.class));
				response.setOnlineStatus(record.get("online_status", Integer.class));
				response.setRecommendStatus(record.get("recommend_status", Integer.class));
				response.setCreateTime(record.get("create_time", Date.class));
				response.setOnlineTime(record.get("online_time", Date.class));
				
				response.setTabNamesText(listToString(getTabListOfArticle(articleId)));
				response.setMiniAppsNamesText(listToString(getMiniAppsListOfArticle(articleId)));
				return response;
			}
		}), pageable, count);
	}
	
	/**
	 * list列表转换为文本字符串
	 * @param list
	 * @return
	 */
	private String listToString(List<MiniAppsRelation> list) {
		if(list == null || list.size() == 0) {
			return null;
		}
		StringBuffer sbf = new StringBuffer();
		for(MiniAppsRelation relation : list) {
			if(sbf.length() == 0) {
				sbf.append(relation.getRelationName());
			}
			sbf.append(","+relation.getRelationName());
		}
		return sbf.toString();
	}
	
	/**
	 * 通过关系类型查找所属关系类型
	 * @param articleId
	 * @param types
	 * @return
	 */
	public List<MiniAppsArticleRelations> selectRelationByArticleIdAndTypeList(Long articleId ,List<String> types) {
		
		List<Object> paramList = Lists.newArrayList();
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT * FROM loan_mini_apps_article_relations ar WHERE ar.ARTICLE_ID = ? AND ar.RELATION_ID in ( ");
		sbf.append(" SELECT id FROM loan_mini_apps_relation r WHERE r.type in ("+listToSqlPart(types)+"))");
		
		paramList.add(articleId);
		return dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsArticleRelations>() {
			@Override
			public MiniAppsArticleRelations map(Record record) {
				MiniAppsArticleRelations relation = new MiniAppsArticleRelations();
				relation.setId(record.get("ID", Long.class));
				relation.setArticleId(record.get("ARTICLE_ID", Long.class));
				relation.setRelationId(record.get("RELATION_ID", Integer.class));
				relation.setWeight(record.get("WEIGHT", Integer.class));
				relation.setOnlineTime(record.get("ONLINE_TIME", Date.class));
				relation.setOfflineTime(record.get("OFFLINE_TIME", Date.class));
				relation.setStatus(record.get("STATUS", Integer.class));
				return relation;
			}
		});
	}
	
	/**
	 * 查找文章所属标签
	 * @param articleId
	 * @return
	 */
	public List<MiniAppsRelation> getTabListOfArticle(Long articleId) {
		List<Object> paramList = Lists.newArrayList();
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT r.ID as id ,r.RELATION_CODE as relation_code ,r.RELATION_NAME AS relation_name ,r.TYPE as type ");
		sbf.append(" FROM loan.loan_mini_apps_article_relations ar, loan.loan_mini_apps_relation r ");
		sbf.append(" WHERE ar.ARTICLE_ID = ? AND ar.RELATION_ID = r.id AND r.TYPE = 'TAB' ");
		
		paramList.add(articleId);
		
		List<MiniAppsRelation> list = dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsRelation>() {
			@Override
			public MiniAppsRelation map(Record record) {
				MiniAppsRelation relation = new MiniAppsRelation();
				relation.setId(record.get("id", Long.class));
				relation.setRelationCode(record.get("relation_code", String.class));
				relation.setRelationName(record.get("relation_name", String.class));
				relation.setType(record.get("type", String.class));
				return relation;
			}
		});
		return list;
	}
	
	/**
	 * 查找文章所属广告位
	 * @param articleId
	 * @return
	 */
	public List<MiniAppsRelation> getAdvertiseListOfArticle(Long articleId) {
		List<Object> paramList = Lists.newArrayList();
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT r.ID as id ,r.RELATION_CODE as relation_code ,r.RELATION_NAME AS relation_name ,r.TYPE as type ");
		sbf.append(" FROM loan.loan_mini_apps_article_relations ar, loan.loan_mini_apps_relation r ");
		sbf.append(" WHERE ar.ARTICLE_ID = ? AND ar.RELATION_ID = r.id AND r.TYPE = 'ADVERTISE' ");
		
		paramList.add(articleId);
		
		List<MiniAppsRelation> list = dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsRelation>() {
			@Override
			public MiniAppsRelation map(Record record) {
				MiniAppsRelation relation = new MiniAppsRelation();
				relation.setId(record.get("id", Long.class));
				relation.setRelationCode(record.get("relation_code", String.class));
				relation.setRelationName(record.get("relation_name", String.class));
				relation.setType(record.get("type", String.class));
				return relation;
			}
		});
		return list;
	}
	
	/**
	 * 查找文章所属小程序
	 * @param articleId
	 * @return
	 */
	public List<MiniAppsRelation> getMiniAppsListOfArticle(Long articleId) {
		List<Object> paramList = Lists.newArrayList();
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT r.ID as id ,r.RELATION_CODE as relation_code ,r.RELATION_NAME AS relation_name ,r.TYPE as type ");
		sbf.append(" FROM loan.loan_mini_apps_article_relations ar, loan.loan_mini_apps_relation r ");
		sbf.append(" WHERE ar.ARTICLE_ID = ? AND ar.RELATION_ID = r.id AND r.TYPE = 'MINI_APPS' ");
		
		paramList.add(articleId);
		
		List<MiniAppsRelation> list = dslContext.fetch(sbf.toString(), paramList.toArray()).map(new RecordMapper<Record, MiniAppsRelation>() {
			@Override
			public MiniAppsRelation map(Record record) {
				MiniAppsRelation relation = new MiniAppsRelation();
				relation.setId(record.get("id", Long.class));
				relation.setRelationCode(record.get("relation_code", String.class));
				relation.setRelationName(record.get("relation_name", String.class));
				relation.setType(record.get("type", String.class));
				return relation;
			}
		});
		return list;
	}
	
	private String listToSqlPart(List<String> list) {
		if(list == null || list.size() == 0) {
			return "";
		}
		StringBuffer sbf = new StringBuffer();
		for(String s : list) {
			if(sbf.length() == 0) {
				sbf.append("'"+s+"'");
			} else {
				sbf.append(",'"+s+"'");
			}
		}
		return sbf.toString();
	}


	/******检查是否查询全部*********/
    private boolean isSelectQueryAll(List selectList) {
    	if(selectList == null || selectList.size() == 0) {
    		return false;
    	}
    	for(int i=0; i<selectList.size(); i++) {
    		String s = String.valueOf(selectList.get(i));
    		if("-1000".equals(s)) {
    			return true;
    		}
    	}
    	return false;
    }

}
























